package com.locki.execDDL.service;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.concurrent.Callable;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

public class Run implements Callable<String> {
	// 日志路径
	private String path = ".";
	// 记录日志
	private FileWriter logfw = null;
	// 该线程执行的数据源
	private DataSource ds;
	// 通过ssh连接时，保存session实例
	private Session session = null;
	// 保存需要执行的DDL
	private Map<String, String> ddlSql = new HashMap<String, String>();

	public void setPath(String path) {
		this.path = path;
	}

	public void setDs(DataSource ds) {
		this.ds = ds;
	}

	public void setDdlSql(Map<String, String> ddlSql) {
		this.ddlSql = ddlSql;
	}

	public String call() throws Exception {
		initLog();
		writeLog("-----------------------------------------《" + ds.getName() + "》线程启动,准备执行脚本-----------------------------------------");
		execDDL();
		writeLog("-----------------------------------------数据源《" + ds.getName() + "》,脚本执行完毕，线程结束-----------------------------------------");
		ExecDDL.destory(logfw);
		if (session != null) {
			session.disconnect();
		}
		return ds.getName() + "执行完成";
	}

	/**
	 * 执行DDL脚本
	 */
	public void execDDL() {
		Connection con = null;
		Statement st = null;
		try {
			
			con = getCon(ds);
			if (con == null) {
				return;
			}
			Iterator<String> it = ddlSql.keySet().iterator();
			while (it.hasNext()) {
				String name = it.next();
				String sqls = ddlSql.get(name);
				try {
					st = con.createStatement();
					String[] sqlArr = sqls.split("@@");
					for (String sql : sqlArr) {
						try {
							if (sql != null && sql.trim().length() > 0) {
								st.executeUpdate(sql);
							}
						} catch (Exception e) {
							writeLog("数据源<" + ds.getName() + ">执行脚本 " + name + " 异常");
							writeLog(ExecDDL.pullException(e));
							writeLog("异常SQL:");
							writeLog(sql);
							ExecDDL.writeTotalLog("数据源<" + ds.getName() + ">执行脚本" + name + "异常", e);
						}
					}
					con.commit();
					writeLog("数据源<" + ds.getName() + ">执行脚本 " + name + " 完毕");
				} catch (Exception e) {
					writeLog("数据源<" + ds.getName() + ">执行脚本" + name + "异常");
					writeLog(ExecDDL.pullException(e));
					ExecDDL.writeTotalLog("数据源<" + ds.getName() + ">执行脚本" + name + "异常", e);
				}
			}
		} catch (Exception e) {
			writeLog("数据源《" + ds.getName() + "》执行异常");
			writeLog(ExecDDL.pullException(e));
			ExecDDL.writeTotalLog("数据源《" + ds.getName() + "》执行异常", e);
		} finally {
			close(null, st, con);
		}
	}

	/**
	 * 初始化实例日志
	 * 
	 * @param proId
	 * @param proName
	 */
	private void initLog() {
		try {
			File log = new File(path + "/" + ds.getId() + "_" + ds.getName() + ".log");
			if (log.exists()) {
				log.delete();
				log.createNewFile();
			}
			this.logfw = new FileWriter(log);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 创建数据库连接
	 * 
	 * @param ds
	 * @return
	 */
	private Connection getCon(DataSource ds) {
		Connection con = null;
		try {
			String url = "jdbc:db2://" + ds.getIp() + ":" + ds.getDbport() + "/" + ds.getDatabase();
			String dbUsername = ds.getDbUsername();
			String dbPassword = ds.getDbPassword();
			Class.forName(ExecDDL.driver);
			if ("2".equals(ds.getStatus())) {
				int port = establishSSH(ds);
				url = "jdbc:db2://127.0.0.1:" + port + "/" + ds.getDatabase();
			}
			con = (Connection) DriverManager.getConnection(url, dbUsername, dbPassword);
		} catch (Exception e) {
			writeLog("数据源《" + ds.getName() + "》连接异常");
			writeLog(ExecDDL.pullException(e));
			ExecDDL.writeTotalLog("数据源《" + ds.getName() + "》连接异常", e);
		}
		return con;
	}
	
	/**
	 * 建立SSH通道
	 * 
	 * @param ds
	 * @return
	 * @throws Exception
	 */
	private int establishSSH(DataSource ds) throws Exception {
		JSch ssh = new JSch();
		int sshPort = 0;
		int dbPort = 0;
		int localPort = 0;
		try {
			sshPort = Integer.parseInt(ds.getSshport());
			dbPort = Integer.parseInt(ds.getDbport());
			localPort = dbPort + Integer.parseInt(ds.getIp().split("\\.")[2]);
		} catch (Exception e) {
			writeLog("数据源《" + ds.getName() + "》数据库或SSH通道端口配置错误");
			writeLog(ExecDDL.pullException(e));
			ExecDDL.writeTotalLog("数据源《" + ds.getName() + "》数据库或SSH通道端口配置错误", e);
		}
		session = ssh.getSession(ds.getSshUusername(), ds.getIp(), sshPort);
		session.setPassword(ds.getSshPassword());
		session.setConfig("StrictHostKeyChecking", "no");
		session.connect();
		// 端口映射 转发
		int assinged_port = session.setPortForwardingL(localPort, ds.getIp(), dbPort);
		writeLog("本地端口 : " + assinged_port);
		return assinged_port;
	}

	/**
	 * 关闭连接
	 * 
	 * @param rs
	 * @param st
	 * @param con
	 */
	private void close(ResultSet rs, Statement st, Connection con) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (st != null) {
				st.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (Exception e2) {
			e2.printStackTrace();
		}
	}

	/**
	 * 记录日志
	 * 
	 * @param msg
	 */
	public void writeLog(String msg) {
		try {
			logfw.write(msg);
			logfw.write("\n");
			logfw.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
